package com.example.fund.fundstocks;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Sheet;
import jxl.Workbook;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.example.fund.Constant;
import com.example.fund.util.AssetsUtil;
import com.example.fund.util.DBManager;
import com.example.fund.util.FundUtil1;
import com.example.fund.util.StringUtil;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class FundStocksUtil1 {
	private static String url1 = "http://fund.eastmoney.com/f10/FundArchivesDatas.aspx?type=jjcc&code=#code&topline=10";
	//中证行业分类架构
	private static String url2 = "http://www.csindex.com.cn/sseportal/ps/zhs/hqjt/csi/ZzhyflJg.xls";
	//中证行业分类表
	private static String url3 = "http://www.csindex.com.cn/sseportal/ps/zhs/hqjt/csi/ZzhyflWz.xls";
	private static String fileName3 = "ZzhyflWz.xls";
	
	private static String url4 = "http://q.10jqka.com.cn/zs/detail/field/199112/order/desc/page/#page/ajax/1/code/#code";
	
	/**
	 * 加载基金十大持仓
	 * @param db
	 * @param code
	 */
	public static void loadFundStocks(String code)
	{
		HttpURLConnection con = null;
		BufferedReader reader = null;
		String s = null;
		
		String dateStr = "";
		try
		{
			String urlStr = url1.replace("#code", code);
			URL url = new URL(urlStr);
			con = (HttpURLConnection) url.openConnection();
			con.setRequestMethod("GET");
			reader = new BufferedReader(new InputStreamReader(con.getInputStream(),"UTF-8"));
			s = reader.readLine().replaceAll("&nbsp;", "");
//			System.out.println(s); 
			//截取日期
			Pattern pattern = Pattern.compile("[0-9]{4}[-][0-9]{1,2}[-][0-9]{1,2}");
			Matcher matcher = pattern.matcher(s);
			if(matcher.find()){  
				dateStr = matcher.group(0);  
			} 
			
			if(s.indexOf("<tbody>")<0 || s.indexOf("</tbody>")<0)
			{
				return;
			}
			s = s.substring(s.indexOf("<tbody>"),s.indexOf("</tbody>")+8);
//			System.out.println(s); 

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
		}
		if(s==null || s.trim().length()==0)
		{
			return;
		}
		
		//2.解析字符串，并计算成累积净值的形式保存

		Document xmlDoc = null;
		try
		{
		//	s = "<root><tbody></tbody></root>";
			InputStream inputStream = new ByteArrayInputStream(s.getBytes("UTF-8"));
			SAXReader sax = new SAXReader();
			xmlDoc = sax.read(inputStream);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		if(xmlDoc==null)
		{
			return;
		}
		Element root = xmlDoc.getRootElement();//根节点
		List trs = root.elements("tr");
		if(trs==null || trs.size()==0)
		{
			return;
		}
		
		String delSql = "delete from FundStocks where fundnumber=?";
//		String delSql = "delete from FundStocks";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into FundStocks (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    fundnumber,	\n");
        insertSql.append("	    date,	\n");
        insertSql.append("	    stocksnumber,	\n");
        insertSql.append("	    stocksname,	\n");
        insertSql.append("	    share	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
        
		SQLiteDatabase db = DBManager.getDB();
		db.beginTransaction();
		try {
			SQLiteStatement stmt = db.compileStatement(insertSql.toString());
			
			SQLiteStatement stmt1 = db.compileStatement(delSql);
			stmt1.bindString(1, code);
		    stmt1.execute();  
		    stmt1.clearBindings(); 
		    
			for(int i=0;i<trs.size();i++)
			{
				Element tr = (Element)trs.get(i);
				List tds = tr.elements("td");
				if(tds.size()<9)
				{
					continue;
				}
				//编码列
				List as = ((Element)tds.get(1)).elements("a");
				if(as==null || as.size()==0)
				{
					continue;
				}
				String number = ((Element)as.get(0)).getTextTrim();
				List as1 = ((Element)tds.get(2)).elements("a");
				if(as1==null || as1.size()==0)
				{
					continue;
				}
				String name = ((Element)as1.get(0)).getTextTrim();
				String share = ((Element)tds.get(6)).getTextTrim();
				if(number==null || name==null || share==null
						|| number.trim().length()==0 || name.trim().length()==0 || share.trim().length()==0)
				{
					continue;
				}
				
		//		System.out.println(number+name+share);
				
				stmt.bindString(1, code);
				stmt.bindString(2, dateStr);
			    stmt.bindString(3, number);  
			    stmt.bindString(4, name);  
			    stmt.bindDouble(5, Double.valueOf(share.replace("%", "")));  
			    stmt.execute();  
			    stmt.clearBindings(); 
			}
			db.setTransactionSuccessful(); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally
		{
			db.endTransaction();
		}
	}
	
	/**
	 * 加载中证行业分类架构
	 * @param db
	 */
	public static boolean loadZZHYFL()
	{
		SQLiteDatabase db = DBManager.getDB();

		HttpURLConnection con = null;
		BufferedReader reader = null;
		List<String> list = new ArrayList<String>();
		try {
			URL url = new URL(url2);
			con = (HttpURLConnection) url.openConnection();
			con.setRequestMethod("GET");

			InputStream is = con.getInputStream();
		    ByteArrayOutputStream output = new ByteArrayOutputStream();
		    byte[] buffer = new byte[1024];
		    int n = 0;
		    while (-1 != (n = is.read(buffer))) {
		        output.write(buffer, 0, n);
		    }
			InputStream inputStream = new ByteArrayInputStream(output.toByteArray());
			Workbook book = Workbook.getWorkbook(inputStream);

			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols<8)
			{
				return false;
			}
			for(int i=0;i<=6;i=i+2)
			{
				for(int j=0;j<rows;j++)
				{
					String number = sheet.getCell(i,j).getContents();
					String name = sheet.getCell(i+1,j).getContents();
					if(number==null || number.trim().length()==0
							|| name==null || name.trim().length()==0)
					{
						continue;
					}
					String value = number+"|"+name;
					if(!list.contains(value))
					{
						list.add(value);
					}
				}
			}
			book.close();
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
		}
		
		if(list.size()==0)
		{
			return false;
		}
		
		String delSql = "delete from zzhyfl";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into zzhyfl (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    number,	\n");
        insertSql.append("	    name	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
		
		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(insertSql.toString());
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		stmt1.execute();
		
		for(int i=0;i<list.size();i++)
		{
			String[] strArr = list.get(i).split("\\|");
			if(strArr.length<2)
			{
				continue;
			}
		    stmt.bindString(1, strArr[0]);  
		    stmt.bindString(2, strArr[1]);  
		    stmt.execute();  
		    stmt.clearBindings(); 
		}
		
		db.setTransactionSuccessful();  
		db.endTransaction(); 
		
		return true;
	}
	
	/**
	 * 加载股票分类
	 * @param db
	 */
	public static void loadStocksFLFromLocalhost(Activity activity)
	{
		SQLiteDatabase db = DBManager.getDB();

		List<String> list = new ArrayList<String>();
		try {
			InputStream inputStream = activity.getResources().getAssets().open(fileName3);
			Workbook book = Workbook.getWorkbook(inputStream);

			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols<14)
			{
				return;
			}
			for(int i=1;i<rows;i=i+1)
			{
				String number = sheet.getCell(0,i).getContents();
				String name = sheet.getCell(1,i).getContents();
				String level1 = sheet.getCell(4,i).getContents();
				String level2 = sheet.getCell(7,i).getContents();
				String level3 = sheet.getCell(10,i).getContents();
				String level4 = sheet.getCell(13,i).getContents();
				list.add(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
				System.out.println(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
			}
			book.close();
		} catch (Exception e) {
			System.out.println(e);
		} finally
		{
		}
		
		if(list.size()==0)
		{
			return;
		}
		
		String delSql = "delete from Stocksfl";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into Stocksfl (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    number,	\n");
        insertSql.append("	    name,	\n");
        insertSql.append("	    level1,	\n");
        insertSql.append("	    level2,	\n");
        insertSql.append("	    level3,	\n");
        insertSql.append("	    level4	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
		
		db.beginTransaction();
		SQLiteStatement stmt = db.compileStatement(insertSql.toString());
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		stmt1.execute();
		
		for(int i=0;i<list.size();i++)
		{
			String[] strArr = list.get(i).split("\\|");
			if(strArr.length<6)
			{
				continue;
			}
		    stmt.bindString(1, strArr[0]);  
		    stmt.bindString(2, strArr[1]);  
		    stmt.bindString(3, strArr[2]);  
		    stmt.bindString(4, strArr[3]);  
		    stmt.bindString(5, strArr[4]);  
		    stmt.bindString(6, strArr[5]);  
		    stmt.execute();  
		    stmt.clearBindings(); 
		}
		
		db.setTransactionSuccessful();  
		db.endTransaction(); 
	}
	
	/**
	 * 加载股票分类
	 * @param db
	 */
	public static boolean loadStocksFL()
	{
		HttpURLConnection con = null;
		BufferedReader reader = null;
		List<String> list = new ArrayList<String>();
		try {
			URL url = new URL(url3);
			con = (HttpURLConnection) url.openConnection();
			con.setRequestMethod("GET");

			InputStream is = con.getInputStream();
		//	InputStream is = new FileInputStream(url3); 
		    ByteArrayOutputStream output = new ByteArrayOutputStream();
		    byte[] buffer = new byte[10];
		    int n = 0;
		    while (-1 != (n = is.read(buffer))) {
		        output.write(buffer, 0, n);
		    }
			InputStream inputStream = new ByteArrayInputStream(output.toByteArray());
			Workbook book = Workbook.getWorkbook(inputStream);

			// 获得第一个工作表对象
			Sheet sheet = book.getSheet(0);
			int rows = sheet.getRows();
			int cols = sheet.getColumns();
			if(cols<14)
			{
				return false;
			}
			for(int i=1;i<rows;i=i+1)
			{
				String number = sheet.getCell(0,i).getContents();
				String name = sheet.getCell(1,i).getContents();
				String level1 = sheet.getCell(4,i).getContents();
				String level2 = sheet.getCell(7,i).getContents();
				String level3 = sheet.getCell(10,i).getContents();
				String level4 = sheet.getCell(13,i).getContents();
				list.add(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
	//			System.out.println(number+"|"+name+"|"+level1+"|"+level2+"|"+level3+"|"+level4);
			}
			book.close();
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally
		{
			if(reader!=null)
			{
				try {
					reader.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if(con!=null)
			{
				con.disconnect();
			}
		}
		
		if(list.size()==0)
		{
			return false;
		}
		
		String delSql = "delete from Stocksfl";
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("	insert into Stocksfl (	\n");
        insertSql.append("	    id,	\n");
        insertSql.append("	    number,	\n");
        insertSql.append("	    name,	\n");
        insertSql.append("	    level1,	\n");
        insertSql.append("	    level2,	\n");
        insertSql.append("	    level3,	\n");
        insertSql.append("	    level4	\n");
        insertSql.append("	) values (	\n");
        insertSql.append("	    null,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?,	\n");
        insertSql.append("	    ?	\n");
        insertSql.append("	)   	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		try {
			db.beginTransaction();
			SQLiteStatement stmt = db.compileStatement(insertSql.toString());
			SQLiteStatement stmt1 = db.compileStatement(delSql);
			stmt1.execute();
			
			for(int i=0;i<list.size();i++)
			{
				String[] strArr = list.get(i).split("\\|");
				if(strArr.length<6)
				{
					continue;
				}
			    stmt.bindString(1, strArr[0]);  
			    stmt.bindString(2, strArr[1]);  
			    stmt.bindString(3, strArr[2]);  
			    stmt.bindString(4, strArr[3]);  
			    stmt.bindString(5, strArr[4]);  
			    stmt.bindString(6, strArr[5]);  
			    stmt.execute();  
			    stmt.clearBindings(); 
			}
			
			db.setTransactionSuccessful(); 
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally
		{
			db.endTransaction();
		}
		
		return true;
	}
	
	/**
	 * 加载指数成分股
	 * @param number
	 * @return
	 */
	public static boolean loadIndexStocks(String number)
	{
		SQLiteDatabase db = DBManager.getDB();
		
		String code1 = null;
		if(number.equals("0000001"))
		{
			code1 = "1A0001";
		}else if(number.startsWith("000"))
		{
			code1 = "1B"+number.substring(3);
		}else
		{
			code1 = number.substring(1);
		}
		
		//1.从网络下载。然后截取成XML格式
		String dataURL = url4.replace("#code", code1);
		int totalPage = 1;
		List<String> indxStocksList = new LinkedList<String>();
		
		String delSql = "delete from indexStocks where indexnumber='"+number+"'";
		String insertSql = "insert into indexStocks(id,stocksnumber,indexnumber) values (null,?,'"+number+"')";
		SQLiteStatement stmt1 = db.compileStatement(delSql);
		SQLiteStatement stmt = db.compileStatement(insertSql);
		
		for(int i=1;i<=totalPage;i++)
		{
			String adataURL = dataURL.replace("#page", i+"");
			HttpURLConnection con = null;
			BufferedReader reader = null;
			
			try
			{
				URL url = new URL(adataURL);
				con = (HttpURLConnection) url.openConnection();
				con.setRequestMethod("GET");
				reader = new BufferedReader(new InputStreamReader(con.getInputStream(),"GBK"));
				boolean start = false;
				boolean end = false;
				StringBuffer sb = new StringBuffer();
				String s = null;
				while((s = reader.readLine())!=null)
				{
					if(s.contains("指数列表"))
					{
						continue;
					}
					if(s.contains("j_addStock"))
					{
						continue;
					}
					if(s.contains("<tbody>"))
					{
						start = true;
					}
					if(start)
					{
						if(!end)
						{
							sb.append(s.trim());
//							System.out.println(s);
						}
						if(s.contains("</tbody>"))
						{
							end = true;
						}
					}
			//		Pattern pattern = Pattern.compile("共(.*)页&");
					Pattern pattern = Pattern.compile("<span class=\"page_info\">(.*)</span>");
					Matcher matcher = pattern.matcher(s);
					if(matcher.find())
					{
						String pageNum = matcher.group(1).trim();
						totalPage = Integer.valueOf(pageNum.substring(pageNum.indexOf('/')+1));
					}
				}
				if(sb.length()==0)
				{
					break;
				}
				
				//2.解析字符串，并计算成累积净值的形式保存

				Document xmlDoc = null;
				try
				{
					InputStream inputStream = new ByteArrayInputStream(sb.toString().getBytes("UTF-8"));
					SAXReader sax = new SAXReader();
					xmlDoc = sax.read(inputStream);
				}
				catch (DocumentException e)
				{
					e.printStackTrace();
				}
				catch (UnsupportedEncodingException e)
				{
					e.printStackTrace();
				}
				if(xmlDoc==null)
				{
					break;
				}
				
				Element tbody = xmlDoc.getRootElement();//根节点
				List trs = tbody.elements("tr");
				for(int j=0;j<trs.size();j++)
				{
					Element tr = (Element)trs.get(j);
					List tds = tr.elements("td");
					if(tds.size()<3)
					{
						continue;
					}
					String stocksNumber = ((Element)tds.get(1)).elementTextTrim("a");
					if(stocksNumber!=null && stocksNumber.length()>0)
					{
						indxStocksList.add(stocksNumber);
					}
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
			finally
			{
				if(reader!=null)
				{
					try {
						reader.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
				if(con!=null)
				{
					con.disconnect();
				}
			}
		}

		db.beginTransaction();
		stmt1.execute();
		try {
			for(int i=indxStocksList.size()-1;i>=0;i--)
			{
				String stocksNumber = indxStocksList.get(i);
				stmt.bindString(1, stocksNumber);  
				stmt.execute();  
				stmt.clearBindings();
			}
			db.setTransactionSuccessful();
		} catch (Exception e){
			e.printStackTrace();
			return false;
		} finally
		{
			db.endTransaction();
		}
		
		return true;
	}
	
	/**
	 * 创建数据表
	 * @param db
	 */
	public static void createTable()
	{
/*        StringBuffer sql = new StringBuffer();
        sql.append("	create table FundStocks (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	fundnumber TEXT,	\n");
        sql.append("	date TEXT,	\n");
        sql.append("	stocksnumber TEXT,	\n");
        sql.append("	stocksname TEXT,	\n");
        sql.append("	share DOUBLE	\n");
        sql.append("	)	\n");
//        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table zzhyfl (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT	\n");
        sql.append("	)	\n");
//        db.execSQL(sql.toString());
        
        sql.setLength(0);
        sql.append("	create table Stocksfl (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	number TEXT,	\n");
        sql.append("	name TEXT,	\n");
        sql.append("	level1 TEXT,	\n");
        sql.append("	level2 TEXT,	\n");
        sql.append("	level3 TEXT,	\n");
        sql.append("	level4 TEXT	\n");
        sql.append("	)	\n");
//        db.execSQL(sql.toString());
        
        String sqlIdx5 = "CREATE INDEX idx_FundStocks_fundNumber ON FundStocks(fundnumber)";
        db.execSQL(sqlIdx5.toString());
        
        String sqlIdx6 = "CREATE INDEX idx_FundStocks_stocksnumber ON FundStocks(stocksnumber)";
        db.execSQL(sqlIdx6.toString());
        
        String sqlIdx7 = "CREATE INDEX idx_zzhyfl_number ON zzhyfl(number)";
        db.execSQL(sqlIdx7.toString());
        
        String sqlIdx8 = "CREATE INDEX idx_Stocksfl_number ON Stocksfl(number)";
        db.execSQL(sqlIdx8.toString());
        
        String sqlIdx9 = "CREATE INDEX idx_Stocksfl_level1 ON Stocksfl(level1)";
        db.execSQL(sqlIdx9.toString());
        
        String sqlIdx10 = "CREATE INDEX idx_Stocksfl_level2 ON Stocksfl(level2)";
        db.execSQL(sqlIdx10.toString());
        
        String sqlIdx11 = "CREATE INDEX idx_Stocksfl_level3 ON Stocksfl(level3)";
        db.execSQL(sqlIdx11.toString());
        
        String sqlIdx12 = "CREATE INDEX idx_Stocksfl_level4 ON Stocksfl(level4)";
        db.execSQL(sqlIdx12.toString());
        
        String sqlIdx13 = "ALTER TABLE 'FundStocks' ADD  'date' TEXT";
        db.execSQL(sqlIdx13.toString());*/
		
		SQLiteDatabase db = DBManager.getDB();
		
		StringBuffer sql = new StringBuffer();
        sql.append("	create table indexStocks (	\n");
        sql.append("	id INTEGER PRIMARY KEY AUTOINCREMENT,	\n");
        sql.append("	stocksnumber TEXT,	\n");
        sql.append("	indexnumber TEXT	\n");
        sql.append("	)	\n");
        db.execSQL(sql.toString());
        
        String sqlIdx13 = "CREATE INDEX idx_indexStocks_stocksnumber ON indexStocks(stocksnumber)";
        db.execSQL(sqlIdx13.toString());
        
        String sqlIdx14 = "CREATE INDEX idx_indexStocks_indexnumber ON indexStocks(indexnumber)";
        db.execSQL(sqlIdx14.toString());
	}
	
	
	public static List<Map<String,Object>> getFundList(String level,String zzhyflNumber,String stocksNumber,String indexNumber,String type,String filterStr)
	{
		List<Map<String,Object>> list = new LinkedList<Map<String,Object>>();
		
		Map<String,String> top3HYFLMap = getTop3HYFL(level);//获取基金前3行业分类
		
		StringBuffer sql = new StringBuffer();
		sql.append("	select 	\n");
		sql.append("	  fund.number, 	\n");
		sql.append("	  fund.name, 	\n");
		sql.append("	  temp.qty,	\n");
		sql.append("	  temp.share	\n");
		sql.append("	from Fund fund 	\n");
		sql.append("	  inner join (	\n");
		if(!StringUtil.isNullOrEmpty(indexNumber))
		{
			sql.append("	    select 	\n");
			sql.append("	      fundstocks.fundnumber, 	\n");
			sql.append("	      count(distinct fundstocks.stocksnumber) qty, 	\n");
			sql.append("	      sum(fundstocks.share) share 	\n");
			sql.append("	    from FundStocks fundstocks	\n");
			sql.append("	      inner join indexStocks indexStocks on fundstocks.stocksnumber = indexStocks.stocksnumber 	\n");
			sql.append("	    where indexStocks.indexnumber='"+indexNumber+"' 	\n");
			sql.append("	    group by fundstocks.fundnumber 	\n");
		}else
		{
			sql.append("	    select 	\n");
			sql.append("	      fundstocks.fundnumber, 	\n");
			sql.append("	      count(*) qty, 	\n");
			sql.append("	      sum(fundstocks.share) share 	\n");
			sql.append("	    from FundStocks fundstocks	\n");
			sql.append("	      inner join Stocksfl stocksfl on fundstocks.stocksnumber = stocksfl.number 	\n");
			sql.append("	    where 1=1 	\n");
			if(!StringUtil.isNullOrEmpty(stocksNumber))
			{
				sql.append("	    and fundstocks.stocksnumber = '"+stocksNumber+"' 	\n");
				sql.append("	    group by fundstocks.fundnumber 	\n");
			}else
			{
				if(!StringUtil.isNullOrEmpty(zzhyflNumber) && !StringUtil.isNullOrEmpty(level))
				{
					sql.append("	    and stocksfl.level"+level+" = '"+zzhyflNumber+"' 	\n");
					sql.append("	    group by fundstocks.fundnumber, stocksfl.level"+level+"	\n");
				}else
				{
					sql.append("	    group by fundstocks.fundnumber \n");
				}
			}
		}
		sql.append("	  ) temp on fund.number = temp.fundnumber 	\n");
		sql.append("	where 1=1	\n");
		if(!StringUtil.isNullOrEmpty(filterStr))
		{
			sql.append("	and fund.name like '%"+filterStr+"%'	\n");
		}
		sql.append("	and temp.qty > 0 	\n");
		if(StringUtil.isNullOrEmpty(stocksNumber) && StringUtil.isNullOrEmpty(zzhyflNumber)
				&& StringUtil.isNullOrEmpty(indexNumber) && StringUtil.isNullOrEmpty(type))
		{
			sql.append("	order by fund.grade desc,fund.pinyin	\n");
		}else if("2".equals(type))
		{
			sql.append("	order by temp.share desc,temp.qty desc,fund.pinyin	\n");
		}else
		{
			sql.append("	order by temp.qty desc,temp.share desc,fund.pinyin	\n");
		}
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		int i = 0;
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            int qty = c.getInt(c.getColumnIndex("qty"));
            double share = c.getDouble(c.getColumnIndex("share"));
            
            Map<String, Object> map = new HashMap<String, Object>(); 
            map.put("number", number); 
            map.put("name", name); 
            map.put("qty", qty); 
            map.put("share", new BigDecimal(share).setScale(2, RoundingMode.HALF_UP));
            map.put("top3HYFL", top3HYFLMap.get(number));
            map.put("correl", "详情");
            map.put("stdev", "详情");
            
            list.add(map);
		}
		c.close();
		
		return list;
	}
	
	public static Map<String,String> getTop3HYFL(String level)
	{
		String alevel = (StringUtil.isNullOrEmpty(level)?"1":level);
		Map<String,List<String>> map = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select * from 					\n");
		sql.append("	(					\n");
		sql.append("	select 					\n");
		sql.append("	    fundstocks.fundnumber,					\n");
		sql.append("	    zzhyfl.name zzhyflname,					\n");
		sql.append("	    count(*) qty,					\n");
		sql.append("	sum(fundstocks.share) share					\n");
		sql.append("	from FundStocks fundstocks					\n");
		sql.append("	inner join Stocksfl stocksfl on fundstocks.stocksnumber=stocksfl.number					\n");
		sql.append("	inner join zzhyfl zzhyfl on stocksfl.level"+alevel+"=zzhyfl.number					\n");
		sql.append("	group by fundstocks.fundnumber,zzhyfl.name					\n");
		sql.append("	) t					\n");
		sql.append("	order by t.fundnumber,t.share desc					\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String fundnumber = c.getString(c.getColumnIndex("fundnumber"));
            String zzhyflname = c.getString(c.getColumnIndex("zzhyflname"));
            int qty = c.getInt(c.getColumnIndex("qty"));
            double share = c.getDouble(c.getColumnIndex("share"));
            
            List<String> list = map.get(fundnumber);
            if(list==null)
            {
            	list = new LinkedList<String>();
            	map.put(fundnumber, list);
            }
            if(list.size()>=3)
            {
            	continue;
            }
            list.add(zzhyflname+"("+qty+","+new BigDecimal(share).setScale(0, RoundingMode.HALF_UP)+"%)");
		}
		c.close();
		
		Map<String,String> map1 = new HashMap<String,String>();
		for(String key:map.keySet())
		{
			List<String> list = map.get(key);
			String str = null;
			for(int i=0;i<list.size();i++)
			{
				if(i==0)
				{
					str = list.get(i);
				}else
				{
					str = str + "," + list.get(i);
				}
			}
			map1.put(key, str);
		}
		
		return map1;
	}
	
	
	/**
	 * 查询行业分类
	 * @param db
	 * @param parentNumber
	 * @param level
	 * @return
	 */
	public static List<String> getZZHYFLList(String parentNumber,String level)
	{
		List<String> list = new LinkedList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select 	\n");
		sql.append("	  number, 	\n");
		sql.append("	  name 	\n");
		sql.append("	from zzhyfl 	\n");
		sql.append("	where 1=1 	\n");
		if(!StringUtil.isNullOrEmpty(parentNumber))
		{
			sql.append("	and number like '"+parentNumber+"%' 	\n");
		}
		if(!StringUtil.isNullOrEmpty(level))
		{
			sql.append("	and length(number)="+Integer.valueOf(level)*2+" 	\n");
		}
		sql.append("	order by number 	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            
            list.add(number+"-"+name);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 根据行业分类查询股票
	 * @param db
	 * @param levelNumber
	 * @param level
	 * @return
	 */
	public static List<String> getStocksList(String levelNumber,String level)
	{
		List<String> list = new LinkedList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select 	\n");
		sql.append("	  number, 	\n");
		sql.append("	  name 	\n");
		sql.append("	from Stocksfl 	\n");
		sql.append("	where 1=1 	\n");
		if(!StringUtil.isNullOrEmpty(levelNumber) && !StringUtil.isNullOrEmpty(level))
		{
			sql.append("	and level"+level+" = '"+levelNumber+"' 	\n");
		}
		sql.append("	order by number 	\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            
            list.add(number+"-"+name);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 获取基金10大持仓股票分类
	 * @param db
	 * @param fundNumber
	 * @return
	 */
	public static List<Map<String,Object>> getFundStocksList(String fundNumber)
	{
		List<Map<String,Object>> list = new LinkedList<Map<String,Object>>();
		StringBuffer sql = new StringBuffer();
		sql.append("	select 				\n");
		sql.append("		fundstocks.stocksnumber,			\n");
		sql.append("		fundstocks.date,			\n");
		sql.append("		fundstocks.stocksname,			\n");
		sql.append("		fundstocks.share,			\n");
		sql.append("		zzhyfl1.name level1name,			\n");
		sql.append("		zzhyfl2.name level2name,			\n");
		sql.append("		zzhyfl3.name level3name,			\n");
		sql.append("		zzhyfl4.name level4name			\n");
		sql.append("	from FundStocks fundstocks				\n");
		sql.append("	left join Stocksfl stocksfl on fundstocks.stocksnumber=stocksfl.number				\n");
		sql.append("	left join zzhyfl zzhyfl1 on stocksfl.level1=zzhyfl1.number				\n");
		sql.append("	left join zzhyfl zzhyfl2 on stocksfl.level2=zzhyfl2.number				\n");
		sql.append("	left join zzhyfl zzhyfl3 on stocksfl.level3=zzhyfl3.number				\n");
		sql.append("	left join zzhyfl zzhyfl4 on stocksfl.level4=zzhyfl4.number				\n");
		sql.append("	where fundstocks.fundnumber='"+fundNumber+"'				\n");
		sql.append("	order by fundstocks.share desc,stocksfl.level4				\n");
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("stocksnumber"));
            String date = c.getString(c.getColumnIndex("date"));
            String name = c.getString(c.getColumnIndex("stocksname"));
            double share = c.getDouble(c.getColumnIndex("share"));
            String level1 = c.getString(c.getColumnIndex("level1name"));
            String level2 = c.getString(c.getColumnIndex("level2name"));
            String level3 = c.getString(c.getColumnIndex("level3name"));
            String level4 = c.getString(c.getColumnIndex("level4name"));
            
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("stocks", StringUtil.dealNull(number)+" "+StringUtil.dealNull(name)+" "+StringUtil.dealNull(share));
            map.put("zzhyfl", StringUtil.dealNull(level1)+"-"+StringUtil.dealNull(level2)+"-"+StringUtil.dealNull(level3)+"-"+StringUtil.dealNull(level4));
            map.put("share", share);
            map.put("date", date);
            
            list.add(map);
		}
		c.close();
		
		return list;
	}

	/**
	 * 获取指数
	 * @return
	 */
	public static List<String> getIndexList()
	{
		List<String> list = new LinkedList<String>();
		String sql = "select number,name from indexSet order by seq";
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            String name = c.getString(c.getColumnIndex("name"));
            
            list.add(number+"-"+name);
		}
		c.close();
		
		return list;
	}
	
	/**
	 * 获取指数
	 * @return
	 */
	public static List<String> getIndexNumberList()
	{
		List<String> list = new LinkedList<String>();
		String sql = "select number from indexSet order by seq";
		
		SQLiteDatabase db = DBManager.getDB();

		Cursor c = db.rawQuery(sql.toString(), null);
		while (c.moveToNext())
		{
            String number = c.getString(c.getColumnIndex("number"));
            
            list.add(number);
		}
		c.close();
		
		return list;
	}
	
	public static void test()
	{
/*		for(int i=0;i<Constant.indexNumbers.length;i++)
		{
			List<Entry<String,Double>> mappingList = FundUtil1.getCorrel(db,Constant.indexNumbers[i][0]);
			String text = "";
			int j=0;
			for(Map.Entry<String,Double> mapping:mappingList)
			{ 
				if(j==0)
				{
					text = Constant.indexNamesMap.get(mapping.getKey())+"("+mapping.getValue()+")";
				}else
				{
					text = text+" "+Constant.indexNamesMap.get(mapping.getKey())+"("+mapping.getValue()+")";
					
				}
				j++;
			} 
		}*/
/*		String testSql1 = "select fundnumber,stocksnumber,stocksname,share from fundstocks order by fundnumber";
		Cursor c1 = db.rawQuery(testSql1.toString(), null);
		int i=0;
		while (c1.moveToNext())
		{
            String fundnumber = c1.getString(c1.getColumnIndex("fundnumber"));
            String stocksnumber = c1.getString(c1.getColumnIndex("stocksnumber"));
            String stocksname = c1.getString(c1.getColumnIndex("stocksname"));
            String share = c1.getString(c1.getColumnIndex("share"));
            i++;
		}
		c1.close();
		
		String testSql2 = "select number,name,level1 from Stocksfl order by number";
		Cursor c2 = db.rawQuery(testSql2.toString(), null);
		int j=0;
		while (c2.moveToNext())
		{
            String number = c2.getString(c2.getColumnIndex("number"));
            String name = c2.getString(c2.getColumnIndex("name"));
            String level1 = c2.getString(c2.getColumnIndex("level1"));
            j++;
		}
		c2.close();*/
	}
}
